package com.itttq.kb.mapper;

import java.lang.reflect.Field;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import com.itttq.kb.common.annotation.Column;
import com.itttq.kb.common.annotation.Column.ColumnType;
import com.itttq.kb.common.annotation.Table;
import com.itttq.kb.model.to.PageParamTo;

/**   
 * @ClassName:  SqlProvider
 * @author: 迷蝶
 * @date:   2017年7月15日 下午7:20:44 
 * @Description: TODO
 *     
 * @Copyright: 2017 <a href="www.itttq.com" target="_blank">www.itttq.com</a> Inc. All rights reserved. 
 */
public class SqlProvider {

	private static final Logger logger = LoggerFactory.getLogger(SqlProvider.class);

	/**
	 * 插入sql，有待改进：1、自动获取主键；2、自动获取id值
	 * @Title: insert
	 * @Description: TODO
	 * @author: 迷蝶
	 * @param: @param bean
	 * @param: @return
	 * @return: String
	 * @throws
	 */
	public String insert(Object bean) {
		Class<?> beanClass = bean.getClass();
		Table table = beanClass.getAnnotation(Table.class);
		if (null == table) {
			// TODO 日志
			return "";
		}
		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO ").append(table.name()).append(" (");
		StringBuilder columns = new StringBuilder();
		StringBuilder params = new StringBuilder();
		try {
			Field[] beanFields = beanClass.getDeclaredFields();
			for (Field field : beanFields) {
				Column column = field.getAnnotation(Column.class);
				if (null == column)
					continue;
				// 设置为true获取private的变量
				if (column.inInsert()) {
					field.setAccessible(true);
					if (columns.length() > 0)
						columns.append(",");
					columns.append("`" + column.name() + "`");
					if (params.length() > 0)
						params.append(",");
					params.append("#{" + field.getName() + "}");
				}
			}
		} catch (Exception e) {
			logger.warn("生成insert sql发生异常 SQL:", sql.toString(), e);
			new RuntimeException("get insert sql is exceptoin:" + e);
		}
		sql.append(columns).append(")").append(" VALUES (").append(params).append(")");
		return sql.toString();
	}

	/**
	 * 更新数据
	 * @Title: update
	 * @Description: TODO
	 * @author: 迷蝶
	 * @param: @param bean
	 * @param: @return
	 * @param: @throws IllegalArgumentException
	 * @param: @throws IllegalAccessException
	 * @return: String
	 * @throws
	 */
	public String update(final Object bean) throws IllegalArgumentException, IllegalAccessException {
		Table table = bean.getClass().getAnnotation(Table.class);
		if (null == table) {
			// TODO 日志
			return "";
		}
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE ").append(table.name()).append(" SET ");
		Field[] beanFields = bean.getClass().getDeclaredFields();
		for (Field field : beanFields) {
			Column column = field.getAnnotation(Column.class);
			if (null == column)
				continue;
			field.setAccessible(true);
			Object val = field.get(bean);
			// 设置为true获取private的变量
			if (!column.inUpdate() || null == val)
				continue;
			// 判断字段类型，string单独处理
			if (column.type() == ColumnType.STRING && StringUtils.isEmpty(val.toString())) {
				continue;
			}
			sql.append("`" + column.name() + "`").append(" = #{").append(field.getName()).append("}, ");
		}
		if (sql.toString().trim().endsWith(","))
			sql = sql.delete(sql.length() - 2, sql.length() - 1);
		sql.append(" WHERE ").append(" id = #{id} ");
		logger.info("生成update SQL:[{}]", sql.toString());
		return sql.toString();
	}

	/**
	 * 删除语句
	 * @Title: delete
	 * @Description: TODO
	 * @author: 迷蝶
	 * @param: @param bean
	 * @param: @return
	 * @return: String
	 * @throws
	 */
	public String delete(Object bean) {
		Table table = bean.getClass().getAnnotation(Table.class);
		if (null == table) {
			// TODO 日志
			return "";
		}
		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM ").append(table.name()).append(" WHERE ").append(" id = #{id} ");
		logger.info("生成delete SQL:[{}]", sql.toString());
		return sql.toString();
	}

	public String find4page(Object bean, PageParamTo ppt) throws IllegalArgumentException, IllegalAccessException {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM ");
		sql.append(getSqlStr(bean));
		String sqlstr = (null == ppt ? sql : sql.append(" LIMIT ").append(ppt.getStart()).append(",").append(ppt.getSize())).toString();
		logger.info("生成query page SQL:[{}]", sqlstr);
		return sqlstr;
	}

	public String getCount(Object bean) throws IllegalArgumentException, IllegalAccessException {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT count(*) FROM ");
		sql.append(getSqlStr(bean));
		System.out.println(sql.toString());
		logger.info("生成query count SQL:[{}]", sql.toString());
		return sql.toString();
	}

	private StringBuilder getSqlStr(Object bean) throws IllegalArgumentException, IllegalAccessException {
		StringBuilder sql = new StringBuilder();
		Table table = bean.getClass().getAnnotation(Table.class);
		if (null == table) {
			// TODO 日志
			return null;
		}
		sql.append(table.name());
		Field[] beanFields = bean.getClass().getDeclaredFields();

		StringBuilder whereSql = new StringBuilder();
		boolean sortByUpdatetime = false;
		for (Field field : beanFields) {
			if(field.getName().equalsIgnoreCase("updateTime")) sortByUpdatetime = true;
			Column column = field.getAnnotation(Column.class);
			if (null == column) continue;
			field.setAccessible(true);
			Object val = field.get(bean);
			// 设置为true获取private的变量
			if (!column.inSelect() || null == val)
				continue;
			// 判断字段类型，string单独处理
			if (column.type() == ColumnType.STRING && StringUtils.isEmpty(val.toString()))
				continue;
			if (whereSql.length() > 0)
				whereSql.append(" and ");
			// 判断是否是模糊查询
			if (column.likeSearch()) {
				whereSql.append("`" + column.name() + "`").append(" LIKE CONCAT('%','").append(val).append("','%' ) ");
			} else {
				whereSql.append("`" + column.name() + "`").append(" = '").append(val).append("' ");
			}
		}
		if (whereSql.length() > 0) {
			sql.append(" WHERE ").append(whereSql);
		}
		if(sortByUpdatetime) {
			sql.append(" ORDER BY update_time ");
		}
		return sql;
	}

	public String findById(Object bean) {
		Table table = bean.getClass().getAnnotation(Table.class);
		if (null == table) {
			// TODO 日志
			return "";
		}
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM ").append(table.name()).append(" WHERE ").append(table.pk()).append(" = #{id} ");
		logger.info("生成query by id SQL:[{}]", sql.toString());
		return sql.toString();
	}
}
